﻿using System;
using System.Text;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

public partial class ExecuteExcel : System.Web.UI.Page
{
    ClassExecute.ClassExecute dt;
    string str = BCDataCenter.Configuration.CurrentConnectionString;
    static string error = "";
    DataTable db;
    protected void Page_Init(object sender, EventArgs e)
    {
        GetData();
        ExportToExcel(db);
    }
    protected void Page_Load(object sender, EventArgs e)
    {
        //if (!IsPostBack)
        //{
        ////    GetData();
        ////    ExportToExcel(db);
           
        //}
    }
    void ExportToExcel(DataTable tempTable)
       {
        hdnRowCount.Value = tempTable.Rows.Count.ToString();
        hdnColCount.Value = tempTable.Columns.Count.ToString();

        StringBuilder strBldrForPivot = new StringBuilder("<script language=JavaScript>" + "\n" + "var PivotList = new Array(" + tempTable.Rows.Count + ");" + "\n");
        int iCnt = 0;
        int iCol = 0;

        foreach (DataRow jobRow in tempTable.Rows)
        {

            iCol = 0;
            strBldrForPivot.Append("PivotList[" + iCnt + "] = new Array(" + tempTable.Columns.Count + ");" + "\n");
            foreach (DataColumn jobCol in tempTable.Columns)
            {
                if (object.ReferenceEquals(jobRow[iCol], DBNull.Value) | jobRow[iCol] == null)
                {
                    strBldrForPivot.Append("PivotList[" + iCnt + "][" + iCol + "] = \"" + " " + "\";" + "\n");
                }
                else if (object.ReferenceEquals(jobRow[iCol].GetType(), typeof(string)))
                {
                    if (jobRow[iCol].ToString() == "")
                    {
                        strBldrForPivot.Append("PivotList[" + iCnt + "][" + iCol + "] = \"" + " " + "\";" + "\n");
                    }
                    else
                    {
                        strBldrForPivot.Append("PivotList[" + iCnt + "][" + iCol + "] = \"" + ((string)jobRow[iCol]).Replace("\\", "\\\\").Trim() + "\";" + "\n");
                    }
                }
                else
                {
                    strBldrForPivot.Append("PivotList[" + iCnt + "][" + iCol + "] = \"" + Convert.ToString(jobRow[iCol]).Replace("\\", "\\\\").Trim() + "\";" + "\n");
                }
                iCol += 1;
            }
            iCnt += 1;
        }

        strBldrForPivot.Append("</script>");

        Page.RegisterStartupScript("PivotListing", strBldrForPivot.ToString());
        
        foreach (DataColumn col in tempTable.Columns)
        {
            if (col.ColumnName == "TVV")
            {
                hndOrientation.Value = hndOrientation.Value + "1$";
            }
            else if (col.ColumnName == "OffAmt")
            {
                hndOrientation.Value = hndOrientation.Value + "4$";
            }
            else if (col.ColumnName == "OffDate")
            {
                hndOrientation.Value = hndOrientation.Value + "2$";
            }
            else
            {
                hndOrientation.Value = hndOrientation.Value + "3$";
            }
            hdnHeading.Value=hdnHeading.Value + col.ColumnName.ToString() + "$";
        }
         hdnHeading.Value=hdnHeading.Value.Substring(0,hdnHeading.Value.Length-1);
         hndOrientation.Value = hndOrientation.Value.Substring(0, hndOrientation.Value.Length - 1);
          

    }
    void GetData()
    {
        
        dt = new ClassExecute.ClassExecute();
        if (Session["StoreName"].ToString()=="GET_DATA_REPORT1")
        {
            db=dt.Get_Data_DataTable(str, ref error, Session["StoreName"].ToString(), CommandType.StoredProcedure, new SqlParameter("@TableName", Session["username"].ToString() + "_CustOffTake"));
        }
       else
        {
            db = dt.Get_Data_DataTable(str, ref error, Session["StoreName"].ToString(), CommandType.StoredProcedure, new SqlParameter("@TableName", Session["username"].ToString() + "_CustOffTake"), new SqlParameter("@sitecode", Session["Site"].ToString()));
        }
            
    }
}
